#coding=gbk

import sys
import string 
import time
import sqlite3
import wx
import os
import re
import ConfigParser
import generictable

def cur_file_dir():

    path = sys.path[0]

    if os.path.isdir(path):
        return path
    elif os.path.isfile(path):
        return os.path.dirname(path)

class SimpleGrid(wx.grid.Grid):
    
    def __init__(self, parent):
        
        wx.grid.Grid.__init__(self, parent, -1)
        
        path = cur_file_dir()
        self.DB_NAME = path + os.sep +'data'        #the sqlite database name 
        
        data = self.get_data()
        
        colLabels = ("STATEMENT_TEXT", "TOTAL_EXECUTION_TIME", "TOTAL_USER_CPU_TIME", "NUMBER_EXECUTIONS")
        rowLabels = range(1, len(data)+1)
        tableBase = generictable.GenericTable(data, rowLabels, colLabels)
        self.SetTable(tableBase)
    
    def get_data(self):
        
        conn = sqlite3.connect(self.DB_NAME)
        c = conn.cursor()
        c.execute('SELECT STATEMENT_TEXT,TOTAL_EXECUTION_TIME,TOTAL_USER_CPU_TIME,NUMBER_EXECUTIONS  FROM SQL_STAT ORDER BY TOTAL_EXECUTION_TIME DESC')
        
        return c.fetchall()

class TestFrame(wx.Frame):
    def __init__(self, parent):
        wx.Frame.__init__(self, parent, -1, "A Grid", 
                size=(800, 600))
        grid = SimpleGrid(self)

class Snapshot():
    
    # The constructor for the class: snapshot
    def __init__(self, db_ip, filename):
        
        path = cur_file_dir()  
        self.DB_NAME = path + os.sep +'data'        #the sqlite database name 
        self.CONFIG = path + os.sep +'config.ini'   #the config file
        self.TYPE = 'EN'                            #the type of the snapshot:EN or CN
        self.IP = db_ip                             #the ip of the database in the snapshot
        self.FILENAME = filename                    #the file name of the snapshot
        
    def get_config(self):
        
        config = ConfigParser.ConfigParser()
        config.read(self.CONFIG)
        values =  config.items('CONFIG_%s' % self.TYPE)

        map = {}
        
        for value in values:
            map[string.upper(value[0])] = string.strip(value[1], '\'')
        
        return map
    
    # Delete the \n and blank
    def convertStr(self, str):

        temp = str.rstrip('\n')
        return temp.strip()
    
    def main_report(self,main_info):
         
        DATABASE_IP = self.IP          
        DATE_STR = time.strftime("%Y-%m-%d", time.localtime())
        TIME_STR = time.strftime("%H:%M:%S", time.localtime())
        
        #get the dict
        dict = self.get_config()        

        # connect to the sqllite db
        conn = sqlite3.connect(self.DB_NAME)
        c = conn.cursor()
        
        # execute the sql 
        c.execute("insert into SNAPSHOT(KEY, DATABASE_IP, DATABASE_NAME, TIMESTAMP, DATE, TIME, OPERATING_SYSTEM, \
            HIGH_WATER_MARK_CONNECTIONS, APPLICATION_CONNECTS, LOCK_WAITS, TIME_DATABASE_WAITED_ON_LOCKS, \
            LOCK_LIST_MEMORY_IN_USE, DEADLOCKS_DETECTED, LOCK_ESCALATIONS, LOCK_TIMEOUTS, SORT_OVERFLOWS, \
            BUFFER_POOL_DATA_LOGICAL_READS, BUFFER_POOL_DATA_PHYSICAL_READS, BUFFER_POOL_INDEX_LOGICAL_READS, \
            BUFFER_POOL_INDEX_PHYSICAL_READS, DIRECT_READS, DIRECT_WRITES, HITS_RADIO) \
            values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", \
            (main_info['key'], DATABASE_IP, main_info[dict['DATABASE_NAME']], main_info[dict['TIMESTAMP']], DATE_STR, TIME_STR, \
            main_info[dict['OPERATING_SYSTEM']], main_info[dict['HIGH_WATER_MARK_CONNECTIONS']], main_info[dict['APPLICATION_CONNECTS']], \
            main_info[dict['LOCK_WAITS']], main_info[dict['TIME_DATABASE_WAITED_ON_LOCKS']], main_info[dict['LOCK_LIST_MEMORY_IN_USE']], \
            main_info[dict['DEADLOCKS_DETECTED']], main_info[dict['LOCK_ESCALATIONS']], main_info[dict['LOCK_TIMEOUTS']], \
            main_info[dict['SORT_OVERFLOWS']], main_info[dict['BUFFER_POOL_DATA_LOGICAL_READS']], main_info[dict['BUFFER_POOL_DATA_PHYSICAL_READS']], \
            main_info[dict['BUFFER_POOL_INDEX_LOGICAL_READS']], main_info[dict['BUFFER_POOL_INDEX_PHYSICAL_READS']], \
            main_info[dict['DIRECT_READS']], main_info[dict['DIRECT_WRITES']], main_info['hits_radio']))
    
        # Save (commit) the changes
        conn.commit()
    
        # We can also close the cursor if we are done with it
        c.close()
        
        # close the connection
        conn.close()    

    def report(self,stat):
        
        #get the dict
        dict = self.get_config()
        
        # connect to the sqllite db
        conn = sqlite3.connect(self.DB_NAME)
        c = conn.cursor()
        
        # execute the sql
        c.execute("insert into SQL_STAT(KEY, NUMBER_EXECUTIONS, NUMBER_COMPILATIONS, WORST_PREPARATION_TIME, BEST_PREPARATION_TIME, \
            INTERNAL_ROWS_DELETED, INTERNAL_ROWS_INSERTED, ROWS_READ, INTERNAL_ROWS_UPDATED, ROWS_WRITTEN, STATEMENT_SORTS, STATEMENT_SORT_OVERFLOWS, \
            TOTAL_SORT_TIME, BUFFER_POOL_DATA_LOGICAL_READS, BUFFER_POOL_DATA_PHYSICAL_READS, BUFFER_POOL_TEMPORARY_DATA_LOGICAL_READS, \
            BUFFER_POOL_TEMPORARY_DATA_PHYSICAL_READS, BUFFER_POOL_INDEX_LOGICAL_READS, BUFFER_POOL_INDEX_PHYSICAL_READS, \
            BUFFER_POOL_TEMPORARY_INDEX_LOGICAL_READS, BUFFER_POOL_TEMPORARY_INDEX_PHYSICAL_READS, TOTAL_EXECUTION_TIME, TOTAL_USER_CPU_TIME, \
            TOTAL_SYSTEM_CPU_TIME, STATEMENT_TEXT) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) " ,\
            (stat['KEY'], stat[dict['NUMBER_EXECUTIONS_STR']], stat[dict['NUMBER_COMPILATIONS_STR']], stat[dict['WORST_PREPARATION_TIME_STR']], \
            stat[dict['BEST_PREPARATION_TIME_STR']], stat[dict['INTERNAL_ROWS_DELETED_STR']], stat[dict['INTERNAL_ROWS_INSERTED_STR']], stat[dict['ROWS_READ_STR']], \
            stat[dict['INTERNAL_ROWS_UPDATED_STR']], stat[dict['ROWS_WRITTEN_STR']], stat[dict['STATEMENT_SORTS_STR']], stat[dict['STATEMENT_SORT_OVERFLOWS_STR']], \
            stat[dict['TOTAL_SORT_TIME_STR']], stat[dict['BUFFER_POOL_DATA_LOGICAL_READS_STR']], stat[dict['BUFFER_POOL_DATA_PHYSICAL_READS_STR']], \
            stat[dict['BUFFER_POOL_TEMPORARY_DATA_LOGICAL_READS_STR']], stat[dict['BUFFER_POOL_TEMPORARY_DATA_PHYSICAL_READS_STR']], \
            stat[dict['BUFFER_POOL_INDEX_LOGICAL_READS_STR']], stat[dict['BUFFER_POOL_INDEX_PHYSICAL_READS_STR']], \
            stat[dict['BUFFER_POOL_TEMPORARY_INDEX_LOGICAL_READS_STR']], stat[dict['BUFFER_POOL_TEMPORARY_INDEX_PHYSICAL_READS_STR']], \
            stat[dict['TOTAL_EXECUTION_TIME_STR']], stat[dict['TOTAL_USER_CPU_TIME_STR']], stat[dict['TOTAL_SYSTEM_CPU_TIME_STR']], stat[dict['STATEMENT_TEXT_STR']]))
        
        # Save (commit) the changes
        conn.commit()
    
        # We can also close the cursor if we are done with it
        c.close()
        
        # close the connection
        conn.close()
    
    def gen_key(self, DATABASE_IP, DB_NAME_VALUE, TIMESTAMP_VALUE):
        
        DATABASE_IP = DATABASE_IP.replace('.', '')
    
        TIMESTAMP_VALUE = TIMESTAMP_VALUE.replace('-', '')
        TIMESTAMP_VALUE = TIMESTAMP_VALUE.replace(' ', '')
        TIMESTAMP_VALUE = TIMESTAMP_VALUE.replace(':', '')
        TIMESTAMP_VALUE = TIMESTAMP_VALUE.replace('.', '')
        TIMESTAMP_VALUE = TIMESTAMP_VALUE.replace('/', '')
    
        key = DATABASE_IP + DB_NAME_VALUE + TIMESTAMP_VALUE
        
        return key    
    
    def run(self):
        
        filename = self.FILENAME
        DATABASE_IP = self.IP
               
        DB_NAME_VALUE = ''
        TIMESTAMP_VALUE = ''
        
        f = file(filename)
        sql_stat = []
        temp = {}
        main_info = {}
        MAIN_TO_READ = True
        BEGIN_TO_READ = False
        ADD_OBJECT_FLAG = False
        COMPLETE_OBJECT_FLAG =False
        
        dict = self.get_config()
        
        MAIN_STRING_VALUE_SET = []
        MAIN_STRING_VALUE_SET.append(dict['DATABASE_NAME'])
        MAIN_STRING_VALUE_SET.append(dict['OPERATING_SYSTEM'])
        MAIN_STRING_VALUE_SET.append(dict['TIMESTAMP'])
        
        MAIN_INT_VALUE_SET = []
        MAIN_INT_VALUE_SET.append(dict['HIGH_WATER_MARK_CONNECTIONS'])
        MAIN_INT_VALUE_SET.append(dict['APPLICATION_CONNECTS'])
        MAIN_INT_VALUE_SET.append(dict['LOCK_WAITS'])
        MAIN_INT_VALUE_SET.append(dict['TIME_DATABASE_WAITED_ON_LOCKS'])
        MAIN_INT_VALUE_SET.append(dict['LOCK_LIST_MEMORY_IN_USE'])
        MAIN_INT_VALUE_SET.append(dict['DEADLOCKS_DETECTED'])
        MAIN_INT_VALUE_SET.append(dict['LOCK_ESCALATIONS'])
        MAIN_INT_VALUE_SET.append(dict['LOCK_TIMEOUTS'])
        MAIN_INT_VALUE_SET.append(dict['SORT_OVERFLOWS'])
        MAIN_INT_VALUE_SET.append(dict['BUFFER_POOL_DATA_LOGICAL_READS'])
        MAIN_INT_VALUE_SET.append(dict['BUFFER_POOL_DATA_PHYSICAL_READS'])
        MAIN_INT_VALUE_SET.append(dict['BUFFER_POOL_INDEX_LOGICAL_READS'])
        MAIN_INT_VALUE_SET.append(dict['BUFFER_POOL_INDEX_PHYSICAL_READS'])
        MAIN_INT_VALUE_SET.append(dict['DIRECT_READS'])
        MAIN_INT_VALUE_SET.append(dict['DIRECT_WRITES'])
        
        INT_VALUE_SET = []
        INT_VALUE_SET.append(dict['NUMBER_EXECUTIONS_STR'])
        INT_VALUE_SET.append(dict['NUMBER_COMPILATIONS_STR'])
        INT_VALUE_SET.append(dict['INTERNAL_ROWS_DELETED_STR'])
        INT_VALUE_SET.append(dict['INTERNAL_ROWS_INSERTED_STR'])
        INT_VALUE_SET.append(dict['ROWS_READ_STR'])
        INT_VALUE_SET.append(dict['INTERNAL_ROWS_UPDATED_STR'])
        INT_VALUE_SET.append(dict['ROWS_WRITTEN_STR'])
        INT_VALUE_SET.append(dict['STATEMENT_SORTS_STR'])
        INT_VALUE_SET.append(dict['STATEMENT_SORT_OVERFLOWS_STR'])
        INT_VALUE_SET.append(dict['TOTAL_SORT_TIME_STR'])
        INT_VALUE_SET.append(dict['BUFFER_POOL_DATA_LOGICAL_READS_STR'])
        INT_VALUE_SET.append(dict['BUFFER_POOL_DATA_PHYSICAL_READS_STR'])
        INT_VALUE_SET.append(dict['BUFFER_POOL_TEMPORARY_DATA_LOGICAL_READS_STR'])
        INT_VALUE_SET.append(dict['BUFFER_POOL_TEMPORARY_DATA_PHYSICAL_READS_STR'])
        INT_VALUE_SET.append(dict['BUFFER_POOL_INDEX_LOGICAL_READS_STR'])
        INT_VALUE_SET.append(dict['BUFFER_POOL_INDEX_PHYSICAL_READS_STR'])
        INT_VALUE_SET.append(dict['BUFFER_POOL_TEMPORARY_INDEX_LOGICAL_READS_STR'])
        INT_VALUE_SET.append(dict['BUFFER_POOL_TEMPORARY_INDEX_PHYSICAL_READS_STR'])
        
        FLOAT_VALUE_SET = []
        FLOAT_VALUE_SET.append(dict['WORST_PREPARATION_TIME_STR'])
        FLOAT_VALUE_SET.append(dict['BEST_PREPARATION_TIME_STR'])
        FLOAT_VALUE_SET.append(dict['TOTAL_EXECUTION_TIME_STR'])
        FLOAT_VALUE_SET.append(dict['TOTAL_USER_CPU_TIME_STR'])
        FLOAT_VALUE_SET.append(dict['TOTAL_SYSTEM_CPU_TIME_STR'])
        
        STRING_VALUE_SET = []
        STRING_VALUE_SET.append(dict['STATEMENT_TEXT_STR'])     
               
        for line in f.readlines():
            
           
            #--read the main message of the snapshot
            if line.find('Bufferpool Snapshot') != -1:
                
                MAIN_TO_READ = False
            
            if MAIN_TO_READ:
                
                if line.find('=') == -1:
                    continue
 
                values = line.split('=')
                value_name = values[0].strip()
                value_content = self.convertStr(values[1])            
                        
                if value_name == dict['DATABASE_NAME']:
                    DB_NAME_VALUE = value_content
                
                if value_name == dict['TIMESTAMP']:
                    TIMESTAMP_VALUE = value_content
                  
                try:
                    if MAIN_STRING_VALUE_SET.index(value_name) != -1:
                        main_info[value_name] = value_content
                        
                except ValueError:
                    pass     
                
                try:
                    if MAIN_INT_VALUE_SET.index(value_name) != -1:
                        main_info[value_name] = int(value_content)
                except ValueError:
                    pass        
            
            main_info['key'] = self.gen_key(DATABASE_IP, DB_NAME_VALUE, TIMESTAMP_VALUE)
            
            if line.find('Dynamic SQL Snapshot Result') != -1:
                
                BEGIN_TO_READ = True
            
            if line.find('Application Snapshot') != -1:
                
                BEGIN_TO_READ = False
            
            if  BEGIN_TO_READ:
                
                if line.find('Number of executions') != -1:
                    ADD_OBJECT_FLAG = True
        
                if ADD_OBJECT_FLAG:
                    
                    if line == '\n':
                        ADD_OBJECT_FLAG = False
                        continue
                    
                    if line.find('Statement text') != -1:
                        COMPLETE_OBJECT_FLAG = True
                    
                    values = line.split('=')
                    value_name = values[0].strip()
                    value_content = self.convertStr(values[1])
                    
                    try:
                        if INT_VALUE_SET.index(value_name) != -1:
                            temp[value_name] = int(value_content)
                    except ValueError:
                        pass
                    
                    try:
                        if FLOAT_VALUE_SET.index(value_name)!= -1:
                            temp[value_name] = float(value_content)
                    except ValueError:
                        pass
                        
                    try:
                        if STRING_VALUE_SET.index(value_name)!= -1:
                            temp[value_name] = value_content
                    except ValueError:
                        pass                      
                    
                    temp['KEY'] = main_info['key']
                    if COMPLETE_OBJECT_FLAG:
                        sql_stat.append(temp)
                        temp = {}
                        ADD_OBJECT_FLAG = False
                        COMPLETE_OBJECT_FLAG = False    
        
        main_info['hits_radio'] = (1 - ((float(main_info[dict['BUFFER_POOL_DATA_PHYSICAL_READS']]) + float(main_info[dict['BUFFER_POOL_INDEX_PHYSICAL_READS']])) / \
            (float(main_info[dict['BUFFER_POOL_DATA_LOGICAL_READS']]) + float(main_info[dict['BUFFER_POOL_INDEX_LOGICAL_READS']]))))*100
        
        self.main_report(main_info)
        
        for stat in sql_stat:
            
            self.report(stat)        

class CancelError(Exception):
    
    def __init__(self, value=''):
        self.value = value
    
    def __str__(self):
        
        return repr(self.value)
        
class TestPanel(wx.Panel):
    
    def __init__(self, parent):

        wx.Panel.__init__(self, parent, -1)
        
        self.DB_IP = ''
        self.FILENAME = ''
        
        sizer = wx.FlexGridSizer(cols=2, hgap=5, vgap=5)
        
        self.dbIpLabel = wx.StaticText(self, -1, "DATABASE IP:")
        self.dbIpText = wx.TextCtrl(self, -1, "")

        self.b = wx.Button(self, -1, "Choose a file: ")       
        self.filenameText = wx.TextCtrl(self, -1, "")
        
        self.analyzeButton = wx.Button(self, -1, "Analyze")
        
        sizer.Add(self.dbIpLabel)
        sizer.Add(self.dbIpText)
        sizer.Add(self.b)
        sizer.Add(self.filenameText)
        sizer.Add(self.analyzeButton)
        
        border = wx.BoxSizer()
        border.Add(sizer, 0, wx.ALL, 20)
        self.SetSizer(border)
        
        self.Bind(wx.EVT_BUTTON, self.OnButton, self.b)
        self.Bind(wx.EVT_BUTTON, self.OnChange, self.analyzeButton)
        
    def OnButton(self, evt):

        # Create the dialog. In this case the current directory is forced as the starting
        # directory for the dialog, and no default file name is forced. This can easilly
        # be changed in your program. This is an 'open' dialog, and allows multitple
        # file selections as well.
        #
        # Finally, if the directory is changed in the process of getting files, this
        # dialog is set up to change the current working directory to the path chosen.
        dlg = wx.FileDialog(
            self, message="Choose a file",
            defaultDir=os.getcwd(), 
            defaultFile="",
            style=wx.OPEN | wx.MULTIPLE | wx.CHANGE_DIR
            )

        # Show the dialog and retrieve the user response. If it is the OK response, 
        # process the data.
        if dlg.ShowModal() == wx.ID_OK:
            path = dlg.GetPath()
            self.filenameText.SetValue(path)
            path = string.replace(path, "\\", "\\\\")
            self.FILENAME = path
            self.DB_IP = self.dbIpText.GetValue()
            
        # Destroy the dialog. Don't do this until you are done with it!
        # BAD things can happen otherwise!
        dlg.Destroy()
    
    def OnChange(self, evt):
        
        try:
            OLD_STR = self.dbIpText.GetValue()
            
            max = 1
            
            dlg = wx.ProgressDialog("Progress dialog example",
                                   "An informative message",
                                   maximum = max,
                                   parent=self,
                                   style = wx.PD_CAN_ABORT
                                    | wx.PD_APP_MODAL
                                    | wx.PD_ELAPSED_TIME
                                    #| wx.PD_ESTIMATED_TIME
                                    | wx.PD_REMAINING_TIME
                                    )
            
            if max == 0:
                raise CancelError()
            
            keepGoing = True
            
            #init
            count = 0     
            (keepGoing, skip) = dlg.Update(count)
            
            snp = Snapshot(self.DB_IP, self.FILENAME);
            snp.run()
            count = count + 1
            (keepGoing, skip) = dlg.Update(count)
            if not keepGoing:
                raise CancelError()           
            dlg.Destroy()
            
            #show the result list
            grid_frame = TestFrame(None)
            grid_frame.Show(True)
                    
        except CancelError, e:
            
            dlg.Destroy()
            
class Frame(wx.Frame):
    """Frame class """
    
    def __init__(self, parent=None, id=-1, pos=wx.DefaultPosition, title='Report'):
        
        """Create a Frame instance and display image."""
        wx.Frame.__init__(self, parent, id, title, pos, (275, 275))
        self.panel = TestPanel(self)

class App(wx.App):
    """Application class."""
    
    def OnInit(self):
        self.frame = Frame()
        ib = wx.IconBundle()
        ib.AddIconFromFile("report.ico",wx.BITMAP_TYPE_ANY)
        self.frame.SetIcons(ib)
        self.frame.Show()
        self.SetTopWindow(self.frame)
        return True

def run():
    app = App(0)
    app.MainLoop()    

def main():
    app = App(0)
    app.MainLoop()
    
if __name__ == '__main__':
    
    main()